
package manageDataBase;
/**
 *
 * 
 */

import entertainmentguide.entity.*;
import java.sql.*;
import java.util.*;


public class DBManager {
    private ArrayList<Restaurant> restaurants = new ArrayList<>();
    private ArrayList<Category> categories = new ArrayList<>();
    private ArrayList<Area> areas = new ArrayList<>();
    private ArrayList<PriceRange> prices = new ArrayList<>();
    private ArrayList<User> users = new ArrayList<>();
    private ArrayList<Reservation> reservations = new ArrayList<>();
    private ArrayList<Rating> ratings = new ArrayList<>();

    private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    private String url = "jdbc:derby:Restaurants";
    private String usr = "jenny";
    private String psw = "jenny";
    private Connection con;
    private Statement stmt;
    
    private String addRestaurantSQL = "INSERT INTO RESTAURANT " +
            "(RESTAURANT_NAME,ADDRESS,PHONE,DESCRIPTION,INFORMATION,PHOTO," +
            "CATEGORY_ID, AREA_ID, PRICE_RANGE_ID) VALUES(?,?,?,?,?,?,?,?,?)";
    private String addCategorySQL ="INSERT INTO CATEGORY (CATEGORY_NAME) VALUES(?)";
    private String addAreaSQL ="INSERT INTO AREA (AREA_NAME) VALUES(?)";
    private String addPricePriceRangeSQL ="INSERT INTO PRICE_RANGE " +
            "(LOWEST_PRICE,HIGHEST_PRICE) VALUES(?,?)";
    private String addUserSQL ="INSERT INTO \"USER\" (USER_NAME,PASSWORD,STATUS) VALUES(?,?,?)";
    private String addReservationSQL ="INSERT INTO RESERVATION " +
            "(USER_ID,REST_ID,RESERVATION_DATE,RESERVATION_TIME,STATUS) VALUES(?,?,?,?,?)";
    private String addRattingSQL ="INSERT INTO RATING " +
            "(USER_ID,REST_ID,AVERAGE_RATE,QUALITY_RATE,PRICES_RATE,SERVICE_RATE) " +
            "VALUES(?,?,?,?,?,?)";

    private String readRestaurantSQL = "SELECT * FROM RESTAURANT";
    private String readCategorySQL = "SELECT * FROM CATEGORY";
    private String readAreaSQL = "SELECT * FROM AREA";
    private String readPriceRangeSQL = "SELECT * FROM PRICE_RANGE";
    private String readUserSQL = "SELECT * FROM \"USER\"";
    private String readReservationSQL = "SELECT * FROM RESERVATION";
    private String readRatingSQL = "SELECT * FROM RATING";

    private String delRestaurantSQL = "DELETE FROM RESTAURANT WHERE REST_ID = ?";
    private String delCategorySQl = "DELETE FROM CATEGORY WHERE CATEGORY_ID = ?";
    private String delAreaSQl = "DELETE FROM AREA WHERE AREA_ID = ?";
    private String delPriceRangeSQl = "DELETE FROM PRICE_RANGE WHERE PRICE_RANGE_ID = ?";
    private String delUserSQL = "DELETE FROM \"USER\" WHERE USER_ID = ?";
    private String delReservationSQL = "DELETE FROM RESERVATION WHERE RES_ID = ? ";
    private String delRatingSQL = "DELETE FROM RATING WHERE USER_ID = ? AND REST_ID = ? ";

    private String updRestaurantSQL = "UPDATE RESTAURANT SET RESTAURANT.RESTAURANT_NAME = ?" +
                    ", RESTAURANT.ADDRESS = ?" + ", RESTAURANT.PHONE = ?" +
                    ", RESTAURANT.DESCRIPTION = ?, RESTAURANT.INFORMATION = ? " +
                    ", RESTAURANT.PHOTO = ?, RESTAURANT.CATEGORY_ID=?, RESTAURANT.AREA_ID = ? "+
                    ", RESTAURANT.PRICE_RANGE_ID=? " +
                    "WHERE RESTAURANT.REST_ID = ?";
    private String updCategorySQL = "UPDATE CATEGORY SET CATEGORY.CATEGORY_NAME = ? " +
            "WHERE CATEGORY.CATEGORY_ID = ?";
    private String updAreaSQL = "UPDATE AREA SET AREA.AREA_NAME = ? " +
            "WHERE AREA.AREA_ID = ?";
    private String updPriceRangeSQL = "UPDATE PRICE_RANGE SET PRICE_RANGE.LOWEST_PRICE = ?," +
            "PRICE_RANGE.HIGHEST_PRICE = ? WHERE PRICE_RANGE.PRICE_RANGE_ID = ?";
    private String updUserSQL = "UPDATE \"USER\" SET \"USER\".USER_NAME = ?, \"USER\".PASSWORD = ?," +
            "\"USER\".STATUS = ? WHERE \"USER\".USER_ID = ?";
    private String updReservationSQL = "UPDATE RESERVATION SET " +
            "RESERVATION.RESERVATION_DATE = ?, RESERVATION.RESERVATION_TIME = ?, " +
            "RESERVATION.STATUS = ? "+
            "WHERE RESERVATION.USER_ID = ? AND RESERVATION.REST_ID = ?";
    private String updRatingSQL = "UPDATE RATING SET " +
            "RATING.AVERAGE_RATE = ?, RATING.QUALITY_RATE = ?, " +
            "RATING.PRICES_RATE = ?, RATING.SERVICE_RATE = ? " +
            "WHERE RATING.USER_ID = ? AND RATING.REST_ID = ?";
    
    private String retrieveRestaurantByIDSQL = "SELECT * FROM RESTAURANT "
            + "WHERE RESTAURANT.REST_ID = ?";
    private String retrieveCategoryByIDSQL = "SELECT * FROM CATEGORY "
            + "WHERE CATEGORY.CATEGORY_ID = ?";
    private String retrieveUserByNameSQL = "SELECT * FROM \"USER\" "
            + "WHERE \"USER\".USER_NAME = ?";
    private String retrieveAreaByIDSQL = "SELECT * FROM AREA "
            + "WHERE AREA.AREA_ID = ?";
    private String retrieveRatingSQL = "SELECT * FROM RATING " 
            + "WHERE RATING.USER_ID = ? AND RATING.REST_ID = ? " ;
     private String retrievePriceRangeByIDSQL = "SELECT * FROM PRICE_RANGE "
            + "WHERE PRICE_RANGE.PRICE_RANGE_ID = ?";
    
    
    private PreparedStatement insertRestaurant;
    private PreparedStatement insertCategory;
    private PreparedStatement insertArea;
    private PreparedStatement insertPriceRange;
    private PreparedStatement insertUser;
    private PreparedStatement insertReservation;
    private PreparedStatement insertRating;

    private PreparedStatement readAllRestaurant;
    private PreparedStatement readAllCategory;
    private PreparedStatement readAllArea;
    private PreparedStatement readAllPriceRange;
    private PreparedStatement readAllUser;
    private PreparedStatement readAllReservation;
    private PreparedStatement readAllRating;

    private PreparedStatement deleteRestaurant;
    private PreparedStatement deleteCategory;
    private PreparedStatement deleteArea;
    private PreparedStatement deletePriceRange;
    private PreparedStatement deleteUser;
    private PreparedStatement deleteReservation;
    private PreparedStatement deleteRating;

    private PreparedStatement updateRestaurant;
    private PreparedStatement updateCategory;
    private PreparedStatement updateArea;
    private PreparedStatement updatePriceRange;
    private PreparedStatement updateUser;
    private PreparedStatement updateReservation;
    private PreparedStatement updateRating;

    private PreparedStatement retrieveRestaurantByID;
    private PreparedStatement retrieveCategoryByID;
    private PreparedStatement retrieveUserByName;
    private PreparedStatement retrieveAreaByID;
    private PreparedStatement retrieveRating;
    private PreparedStatement retrievePriceRangeByID; 

    
    public DBManager()
    {
        try {
            Class.forName(driver);                                              //'Φόρτωμα' driver
            con = DriverManager.getConnection(url, usr, psw);   
            
            //Δημιουργία σύνδεσης
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            
            insertRestaurant = con.prepareStatement(addRestaurantSQL,Statement.RETURN_GENERATED_KEYS);
            insertCategory = con.prepareStatement(addCategorySQL,Statement.RETURN_GENERATED_KEYS);
            insertArea = con.prepareStatement(addAreaSQL,Statement.RETURN_GENERATED_KEYS);
            insertPriceRange = con.prepareStatement(addPricePriceRangeSQL,Statement.RETURN_GENERATED_KEYS);
            insertUser = con.prepareStatement(addUserSQL,Statement.RETURN_GENERATED_KEYS);
            insertReservation = con.prepareStatement(addReservationSQL,Statement.RETURN_GENERATED_KEYS);
            insertRating = con.prepareStatement(addRattingSQL,Statement.RETURN_GENERATED_KEYS);

            readAllRestaurant = con.prepareStatement(readRestaurantSQL);
            readAllCategory = con.prepareStatement(readCategorySQL);
            readAllArea = con.prepareStatement(readAreaSQL);    
            readAllPriceRange = con.prepareStatement(readPriceRangeSQL) ;
            readAllUser = con.prepareStatement(readUserSQL);    
            readAllReservation = con.prepareStatement(readReservationSQL);  
            readAllRating = con.prepareStatement(readRatingSQL);    

            deleteRestaurant = con.prepareStatement(delRestaurantSQL);
            deleteCategory = con.prepareStatement(delCategorySQl);  
            deleteArea = con.prepareStatement(delAreaSQl);  
            deletePriceRange = con.prepareStatement(delPriceRangeSQl);  
            deleteUser = con.prepareStatement(delUserSQL);
            deleteReservation = con.prepareStatement(delReservationSQL);
            deleteRating = con.prepareStatement(delRatingSQL);

            updateRestaurant = con.prepareStatement(updRestaurantSQL);
            updateCategory = con.prepareStatement(updCategorySQL);
            updateArea = con.prepareStatement(updAreaSQL);
            updatePriceRange = con.prepareStatement(updPriceRangeSQL);
            updateUser = con.prepareStatement(updUserSQL);
            updateReservation = con.prepareStatement(updReservationSQL);
            updateRating = con.prepareStatement(updRatingSQL);

            retrieveRestaurantByID = con.prepareStatement(retrieveRestaurantByIDSQL);
            retrieveCategoryByID = con.prepareStatement(retrieveCategoryByIDSQL);
            retrieveUserByName = con.prepareStatement(retrieveUserByNameSQL);
            retrieveAreaByID = con.prepareStatement(retrieveAreaByIDSQL);
            retrieveRating = con.prepareStatement(retrieveRatingSQL);
            retrievePriceRangeByID= con.prepareStatement(retrievePriceRangeByIDSQL);
            
        }
        catch (ClassNotFoundException cnfex) {                                       //Χειρισμός λαθών
            System.err.println(cnfex);
            System.exit(1);
        }
        catch(SQLException sqlex) {
            
            sqlex.printStackTrace();
            System.exit(1);
           }

    }
    
    public void closeDB() throws SQLException{
        stmt.closeOnCompletion();
        con.close();
    }

    public void addRestaurant(Restaurant r, Category c, Area a, PriceRange p) throws SQLException   //Εισαγωγή εστιατορίου
    {
            insertRestaurant.setString(1, r.getRestaurantName());
            insertRestaurant.setString(2, r.getAddress());
            insertRestaurant.setString(3, r.getPhone());
            insertRestaurant.setString(4, r.getDescription());
            insertRestaurant.setString(5, r.getInformation());
            insertRestaurant.setString(6, r.getPhoto());
            insertRestaurant.setInt(7, c.getCategoryId());
            insertRestaurant.setInt(8, a.getAreaId());
            insertRestaurant.setInt(9, p.getPriceRangeId());
            insertRestaurant.executeUpdate();

    }

    public void addRestaurant(String name, String add, String ph, String des, String inf, String photo, int catId, int arId, int prId) throws SQLException   //Εισαγωγή εστιατορίου
    {
            insertRestaurant.setString(1, name);
            insertRestaurant.setString(2, add);
            insertRestaurant.setString(3, ph);
            insertRestaurant.setString(4, des);
            insertRestaurant.setString(5, inf);
            insertRestaurant.setString(6, photo);
            insertRestaurant.setInt(7, catId);
            insertRestaurant.setInt(8, arId);
            insertRestaurant.setInt(9, prId);
            insertRestaurant.executeUpdate();

    }
    
     public int  addRestaurantReturnId(String name, String add, String ph, String des, String inf, String photo, int catId, int arId, int prId) throws SQLException   //Εισαγωγή εστιατορίου
    {
            insertRestaurant.setString(1, name);
            insertRestaurant.setString(2, add);
            insertRestaurant.setString(3, ph);
            insertRestaurant.setString(4, des);
            insertRestaurant.setString(5, inf);
            insertRestaurant.setString(6, photo);
            insertRestaurant.setInt(7, catId);
            insertRestaurant.setInt(8, arId);
            insertRestaurant.setInt(9, prId);
            insertRestaurant.executeUpdate();
            //get generated ID
            ResultSet rs = insertRestaurant.getGeneratedKeys();
            rs.next();
            return  rs.getInt(1);
 
    }
    
     public void  addRestaurant(Restaurant r) throws SQLException   //Εισαγωγή εστιατορίου
    {
            insertRestaurant.setString(1, r.getRestaurantName());
            insertRestaurant.setString(2, r.getAddress());
            insertRestaurant.setString(3, r.getPhone());
            insertRestaurant.setString(4, r.getDescription());
            insertRestaurant.setString(5, r.getInformation());
            insertRestaurant.setString(6, r.getPhoto());
            insertRestaurant.setInt(7, r.getCategoryId());
            insertRestaurant.setInt(8, r.getAreaId());
            insertRestaurant.setInt(9, r.getPriceRangeId());
            insertRestaurant.executeUpdate();
            

    }
    
      //Εισαγωγή κατηγορίας
    public void addCategory(Category c) throws SQLException                         
    {
            insertCategory.setString(1, c.getCategoryName());
            insertCategory.executeUpdate();
    }
    
    //Εισαγωγή κατηγορίας επιστρέφει ID
       public int addCategoryReturnID(Category c) throws SQLException                
    {
            insertCategory.setString(1, c.getCategoryName());
            insertCategory.executeUpdate();
            //get generated ID
            ResultSet rs = insertCategory.getGeneratedKeys();
            rs.next();
            return  rs.getInt(1);  
            
    }

    //Εισαγωγή περιοχής
    public void addArea(Area a) throws SQLException                                  
    {
            insertArea.setString(1, a.getAreaName());
            insertArea.executeUpdate();
    }

    //Εισαγωγή περιοχής επιστρέφει ID
    public int addAreaReturnID(Area a) throws SQLException                                  
    {
            insertArea.setString(1, a.getAreaName());
            insertArea.executeUpdate();
            //get generated ID
            ResultSet rs = insertArea.getGeneratedKeys();
            rs.next();
            return  rs.getInt(1); 
    }
    
    //Εισαγωγή εύρους τιμών
    public void addPriceRange(PriceRange p) throws SQLException                     
    {
            insertPriceRange.setDouble(1, p.getLowestPrice());
            insertPriceRange.setDouble(2, p.getHighestPrice());
            insertPriceRange.executeUpdate();
    }
    
     //Εισαγωγή εύρους τιμών επιστρέφει ID
    public int addPriceRangeReturnID(PriceRange p) throws SQLException      
    {
            insertPriceRange.setDouble(1, p.getLowestPrice());
            insertPriceRange.setDouble(2, p.getHighestPrice());
            insertPriceRange.executeUpdate();
            //get generated ID
            ResultSet rs = insertPriceRange.getGeneratedKeys();
            rs.next();
            return  rs.getInt(1); 
    }

    //Εισαγωγή χρήστη
    public void addUser(User u) throws SQLException                                 
    {
            insertUser.setString(1, u.getUserName());
            insertUser.setString(2, u.getPassword());
            insertUser.setInt(3, u.getStatus());
            insertUser.executeUpdate();
    }
    
    //Εισαγωγή χρήστη επιστρέφει ID
    public int addUserReturnID(User u) throws SQLException                               
    {
            insertUser.setString(1, u.getUserName());
            insertUser.setString(2, u.getPassword());
            insertUser.setInt(3, u.getStatus());
            insertUser.executeUpdate();
            //get generated ID
            ResultSet rs = insertUser.getGeneratedKeys();
            rs.next();
            return  rs.getInt(1); 
    }
     
    //Εισαγωγή κράτησης
    public void addReservation(Reservation v) throws SQLException                    
    {
            insertReservation.setInt(1, v.getUserId());
            insertReservation.setInt(2, v.getRestId());
            insertReservation.setDate(3, v.getRDate());
            insertReservation.setTime(4, v.getRTime());
            insertReservation.setInt(5, v.getStatus());
            insertReservation.executeUpdate();
    }
    
    //Εισαγωγή κράτησης επιστρέφει ID
    public int addReservationReturnID(Reservation v) throws SQLException                    
    {
            insertReservation.setInt(1, v.getUserId());
            insertReservation.setInt(2, v.getRestId());
            insertReservation.setDate(3, v.getRDate());
            insertReservation.setTime(4, v.getRTime());
            insertReservation.setInt(5, v.getStatus());
            insertReservation.executeUpdate();
            //get generated ID
            ResultSet rs = insertReservation.getGeneratedKeys();
            rs.next();
            return  rs.getInt(1); 
    }
    

    public void addRating(Rating r) throws SQLException                              //Εισαγωγή αξιολόγησης
    {
            insertRating.setInt(1, r.getUserId());
            insertRating.setInt(2, r.getRestId());
            insertRating.setInt(3, r.getAverageRate());
            insertRating.setInt(4, r.getQualityRate());
            insertRating.setInt(5, r.getPricesRate());
            insertRating.setInt(6, r.getServiceRate());
            insertRating.executeUpdate();
    }

    public void delRestaurant(Restaurant r) throws SQLException
    {
            deleteRestaurant.setInt(1, r.getRestId());
            deleteRestaurant.executeUpdate();
    }

    public void delRestaurant(int resId) throws SQLException
    {
            deleteRestaurant.setInt(1, resId);
            deleteRestaurant.executeUpdate();
    }

    public void delCategory(Category c) throws SQLException
    {
            deleteCategory.setInt(1, c.getCategoryId());
            deleteCategory.executeUpdate();
    }
    
    public void delArea(Area a) throws SQLException
    {
            deleteArea.setInt(1, a.getAreaId());
            deleteArea.executeUpdate();
    }

    public void delPriceRange(PriceRange p) throws SQLException
    {
            deletePriceRange.setInt(1, p.getPriceRangeId());
            deletePriceRange.executeUpdate();
    }

    public void delUser(User u) throws SQLException
    {
            deleteUser.setInt(1, u.getUserId());
            deleteUser.executeUpdate();
    }

    public void delReservation(Reservation v) throws SQLException
    {
            deleteReservation.setInt(1, v.getResId());
            deleteReservation.executeUpdate();
    }

    public void delRating(Rating r) throws SQLException
    {
            deleteRating.setInt(1, r.getUserId());
            deleteRating.setInt(2, r.getRestId());
            deleteRating.executeUpdate();
    }

    public void updateRestaurant(Restaurant r) throws SQLException
    {
        updateRestaurant.setString(1, r.getRestaurantName());
        updateRestaurant.setString(2, r.getAddress());
        updateRestaurant.setString(3, r.getPhone());
        updateRestaurant.setString(4, r.getDescription());
        updateRestaurant.setString(5, r.getInformation());
        updateRestaurant.setString(6, r.getPhoto());
        updateRestaurant.setInt(7, r.getCategoryId());
        updateRestaurant.setInt(8, r.getAreaId());
        updateRestaurant.setInt(9, r.getPriceRangeId());
        updateRestaurant.setInt(10, r.getRestId());
        updateRestaurant.executeUpdate();
    }

    public void updateCategory(Category c) throws SQLException
    {
        updateCategory.setString(1, c.getCategoryName());
        updateCategory.setInt(2, c.getCategoryId());
        updateRestaurant.executeUpdate();
    }

    public void updateArea(Area a) throws SQLException
    {
        updateArea.setString(1, a.getAreaName());
        updateArea.setInt(2, a.getAreaId());
        updateArea.executeUpdate();
    }

    public void updatePriceRange(PriceRange p) throws SQLException
    {
        updatePriceRange.setDouble(1, p.getLowestPrice());
        updatePriceRange.setDouble(2, p.getHighestPrice());
        updatePriceRange.setDouble(3, p.getPriceRangeId());
        updatePriceRange.executeUpdate();
    }

    public void updateUser(User u) throws SQLException
    {
        updateUser.setString(1, u.getUserName());
        updateUser.setString(2, u.getPassword());
        updateUser.setInt(3, u.getStatus());
        updateUser.executeUpdate();
    }

    public void updateReservation(Reservation v) throws SQLException
    {
        updateReservation.setInt(1, v.getUserId());
        updateReservation.setInt(2, v.getRestId());
        updateReservation.setDate(3, v.getRDate());
        updateReservation.setTime(4, v.getRTime());
        updateReservation.setInt(5, v.getStatus());
        updateReservation.executeUpdate();
    }

    public void updateRating(Rating r) throws SQLException
    {

        updateRating.setInt(1, r.getAverageRate());
        updateRating.setInt(2, r.getQualityRate());
        updateRating.setInt(3, r.getPricesRate());
        updateRating.setInt(4, r.getServiceRate());
        updateRating.setInt(5, r.getUserId());
        updateRating.setInt(6, r.getRestId());
        updateRating.executeUpdate();
    }
    
    public ArrayList<Restaurant> getAllRestaurants() throws SQLException                //Επιστρέφει ένα ArrayList με όλες τις
    {                                                                               //τρέχουσες εγγραφές
        Restaurant tmp;
        restaurants.clear();
            ResultSet rs = readAllRestaurant.executeQuery();
            while(rs.next())
            {
                tmp = new Restaurant(rs.getInt(1),rs.getString(2),rs.getString(3),
                        rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getInt(9),rs.getInt(10));
                restaurants.add(tmp);
            }
        return restaurants;
    }

    public ArrayList<Category> getAllCategories() throws SQLException
    {
        Category tmp;
        categories.clear();
            ResultSet rs = readAllCategory.executeQuery();
            while(rs.next())
            {
                tmp = new Category(rs.getInt(1),rs.getString(2));
                categories.add(tmp);
            }
        return categories;
    }

    public ArrayList<Area> getAllAreas() throws SQLException
    {                                                                           
        Area tmp;
        areas.clear();
            ResultSet rs = readAllArea.executeQuery();
            while(rs.next())
            {
                tmp = new Area(rs.getInt(1),rs.getString(2));
                areas.add(tmp);
            }
        return areas;
    }

    public ArrayList<PriceRange> getAllPriceRanges() throws SQLException
    {                                                                           
        PriceRange tmp;
        prices.clear();
            ResultSet rs = readAllPriceRange.executeQuery();
            while(rs.next())
            {
                tmp = new PriceRange(rs.getInt(1),rs.getDouble(2),rs.getDouble(3));
                prices.add(tmp);
            }
        return prices;
    }

    public ArrayList<User> getAllUsers() throws SQLException
    {                                                                         
        User tmp;
        users.clear();
            ResultSet rs = readAllUser.executeQuery();
            while(rs.next())
            {
                tmp = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4));
                users.add(tmp);
            }
        return users;
    }

    public ArrayList<Reservation> getAllReservations() throws SQLException
    {                                                                           
        Reservation tmp;
        reservations.clear();
            ResultSet rs = readAllReservation.executeQuery();
            while(rs.next())
            {
              tmp = new Reservation(rs.getInt(1),rs.getInt(2), rs.getInt(3),
                rs.getDate(4),rs.getTime(5),rs.getInt(6));      
                reservations.add(tmp);
            }
        return reservations;
    }

    public ArrayList<Rating> getAllRatings() throws SQLException
    {                                                                          
        Rating tmp;
        ratings.clear();
            ResultSet rs = readAllRating.executeQuery();
            while(rs.next())
            {
                tmp = new Rating(rs.getInt(1),rs.getInt(2),rs.getInt(3),
                        rs.getInt(4),rs.getInt(5),rs.getInt(6));
                ratings.add(tmp);
            }
        return ratings;
    }
    
    public Restaurant getRestaurantById(int id) throws SQLException
    {
        Restaurant tmp;
        restaurants.clear();
        retrieveRestaurantByID.setInt(1,id);
            ResultSet rs = retrieveRestaurantByID.executeQuery();
            while(rs.next())
            {
                tmp = new Restaurant(rs.getInt("REST_ID"),rs.getString("RESTAURANT_NAME"),
                       rs.getString("ADDRESS"),rs.getString("PHONE"),rs.getString("DESCRIPTION")
                       ,rs.getString("INFORMATION"),rs.getString("PHOTO"),rs.getInt("CATEGORY_ID"),rs.getInt("AREA_ID"),rs.getInt("PRICE_RANGE_ID"));
                restaurants.add(tmp);
            }
            
        return (restaurants.isEmpty()? null :restaurants.get(0));
    }

    public Category getCategoryById(int id) throws SQLException
    {
        Category tmp;
        categories.clear();
        retrieveCategoryByID.setInt(1,id);
            ResultSet rs = retrieveCategoryByID.executeQuery();
            while(rs.next())
            {
                tmp = new Category(rs.getInt("CATEGORY_ID"),rs.getString("CATEGORY_NAME"));
                categories.add(tmp);
            }
        return (categories.isEmpty()? null :categories.get(0));
    }
    
        public Area getAreaById(int id) throws SQLException
    {
        Area tmp;
        areas.clear();
        retrieveAreaByID.setInt(1,id);
            ResultSet rs = retrieveAreaByID.executeQuery();
            while(rs.next())
            {
                tmp = new Area(rs.getInt("AREA_ID"),rs.getString("AREA_NAME"));
                areas.add(tmp);
            }
        return (areas.isEmpty()? null :areas.get(0));
    }
        
    
    public Rating getRating(int userID, int restaurantID) throws SQLException
    {
        Rating tmp;
        ratings.clear();
        retrieveRating.setInt(1,userID);
        retrieveRating.setInt(2,restaurantID);
            ResultSet rs = retrieveRating.executeQuery();
            while(rs.next())
            {
                tmp = new Rating(rs.getInt("USER_ID"),rs.getInt("REST_ID"),rs.getInt("AVERAGE_RATE"),rs.getInt("QUALITY_RATE"),rs.getInt("PRICES_RATE"),rs.getInt("SERVICE_RATE"));
                ratings.add(tmp);
            }
        return (ratings.isEmpty()? null :ratings.get(0));
        
    }
 
     public PriceRange getPriceRangeById(int PriceRangeID) throws SQLException
    {
        PriceRange tmp;
        prices.clear();
        retrievePriceRangeByID.setInt(1,PriceRangeID);
      
            ResultSet rs = retrievePriceRangeByID.executeQuery();
            while(rs.next())
            {
                tmp = new PriceRange(rs.getInt("PRICE_RANGE_ID"),rs.getInt("LOWEST_PRICE"),rs.getInt("HIGHEST_PRICE"));
                prices.add(tmp);
            }
        return (prices.isEmpty()? null :prices.get(0));
    }
                
    
    // επιστρέφει τον χρήστη με βάση το όνομα
    // αν δεν βρεθεί επιστρέφει null
    public User getUserByName(String name) throws SQLException{
        User tmp ;
        users.clear();
        retrieveUserByName.setString(1, name);
        ResultSet rs = retrieveUserByName.executeQuery();
        while(rs.next())
            {
                tmp=new User(rs.getInt("USER_ID"),rs.getString("USER_NAME"),
                        rs.getString("PASSWORD"),rs.getInt("STATUS"));
                users.add(tmp);
            } 
        return (users.isEmpty()? null :users.get(0));
    }
    

// Mέθοδος για τον καθαρισμό της ΒΔ για πολλαπλή εκτέλεση
    public void clearDB() throws SQLException     {
            stmt.executeUpdate("DELETE FROM RESERVATION");
            stmt.executeUpdate("DELETE FROM RATING");
            stmt.executeUpdate("DELETE FROM \"USER\"");
            stmt.executeUpdate("DELETE FROM RESTAURANT");
            stmt.executeUpdate("DELETE FROM AREA");
            stmt.executeUpdate("DELETE FROM CATEGORY");
            stmt.executeUpdate("DELETE FROM PRICE_RANGE");

    }

}
